if exists (select null from sys.procedures where name = 'sp_add_product_to_terminal')
	drop procedure sp_add_product_to_terminal;
go

create procedure sp_add_product_to_terminal
	@prod_code varchar(12), 
	@term_code varchar(12), 
	@dt_beg date,
	@dt_end date,
	@def_prc float,
	@fix money,
	@profile_id int
as
begin
	set nocount on;

	declare @prod_id int set @prod_id = null;
	declare @term_id int set @term_id = null;
	
	select @prod_id = id from products  where code = @prod_code;
	select @term_id = id from terminals where code = @term_code;

	begin tran prod2term;
		declare @prev_id int set @prev_id = null;
		declare @prev_dt_beg datetime, @prev_dt_end datetime;
		select @prev_id = id, @dt_beg = dt_beg, @dt_end = isnull(dt_end, @dt_beg) 
		  from terminal_products 
		 where terminal_id = @term_id and product_id = @prod_id and @dt_beg between dt_beg and isnull(dt_end, @dt_beg);
		 
		if @prev_id is not null
		begin
			if @prev_dt_beg < @prev_dt_end
			begin
				update terminal_products set dt_end = @dt_beg where id = @prev_id;
			end else
			begin
				-- time confilct 
				print 'sp_add_product_to_terminal: time confilct';
				rollback tran prod2term;
				return;
			end;
		end;
	
		insert into terminal_products(terminal_id, product_id, profile_id, dt_beg, dt_end, def_prc, fix)
		values(@term_id, @prod_id, @profile_id, @dt_beg, @dt_end, @def_prc, @fix);
	commit tran prod2term;
end;
go

grant execute on sp_add_product_to_terminal to prg;